#delimit;
clear all;
set more off;

capture log using "07_DAF_Classification_Summary_Table.log", replace;
/*******************************************************************************************/;
** INPUT: Handcode DAF classification merged back with DAF financials; 
** OUTPUT: Table 1(Summary of unique DAF characteristics), Table 2(DAF-year obs. with key stats); 
** LAST MODIFIED: JAG, 8/29/2021;
** RUN TIME: <5 minutes using Stata 15 on computer with 2 x 2.60GHz processors and 64GB RAM;  													      	
/*******************************************************************************************/;


use "DAF classification clean.dta", clear;

gen top20=0;
replace top20=1 if ein=="06-0646973";
replace top20=1 if ein=="94-1156365";
replace top20=1 if ein=="04-2103580";
replace top20=1 if ein=="21-0634501";
replace top20=1 if ein=="35-0868188";
replace top20=1 if ein=="36-2167817";
replace top20=1 if ein=="13-5598093";
replace top20=1 if ein=="23-1352685";
replace top20=1 if ein=="21-0634501";
replace top20=1 if ein=="02-0222111";
replace top20=1 if ein=="05-0258809";
replace top20=1 if ein=="04-2103594";
replace top20=1 if ein=="43-0653611";
replace top20=1 if ein=="95-2250801";
replace top20=1 if ein=="52-0595110";
replace top20=1 if ein=="04-2103542";
replace top20=1 if ein=="16-0743209";
replace top20=1 if ein=="95-1642394";
replace top20=1 if ein=="04-2104847";
replace top20=1 if ein=="59-0974739";
gen endow=(sic=="8221");
replace endow=1 if top20==1 & endow!=1;
replace naics_desc=trim(lower(naics_desc));
replace endow=1 if regexm(naics_desc,"school")==1 & endow!=1;
replace endow=1 if regexm(naics_desc,"university")==1 & endow!=1;
replace endow=1 if regexm(naics_desc,"college")==1 & endow!=1;

** Adding in the DAF financials to do the other table;
merge 1:m ein using "Main with additional details.dta";
drop if _m!=3;
replace num_intl_grants=0 if num_intl_grants==.;
gen num_tot_grants=num_dom_grants+num_intl_grants;
sum num_tot_grants, d;

tab endow, m;
tab endow if num_tot_grants==.;

foreach var in grants_orgs_us grants_individuals_us grants_orgs_non_us {;
	replace `var'=0 if `var'==.;
};
gen total_grants= grants_orgs_us+ grants_individuals_us+ grants_orgs_non_us;
egen max_grants=rowmax(total_grants sum_dom_grant_amt);
sum max_grants, d;
replace max_grants=0 if max_grants<0;
count if num_tot_grants==. & total_grants>0;

*drop if total_grants==0 & num_tot_grants==.;

** TABLE 1;
** IRS classification = All, community, single-issue, grantmaking sponsoring org.;
count;
tab comm_daf, m;
tab issue_daf, m;
tab nso_daf, m;

** Audience targeted;
tab is_national, m;
tab is_inclu, m;

tab is_national if comm_daf==1;
tab is_inclu if comm_daf==1;

tab is_national if issue_daf==1;
tab is_inclu if issue_daf==1;

tab is_national if nso_daf==1;
tab is_inclu if nso_daf==1;

** Style of services offered;
gen neither=(style_trad==0 & style_innov==0 & style_disrupt==0);
tab style_trad, m;
tab style_innov, m;
tab style_disrupt, m;
tab neither, m;

tab style_trad if comm_daf==1;
tab style_innov if comm_daf==1;
tab style_disrupt if comm_daf==1;
tab neither if comm_daf==1;

tab style_trad if issue_daf==1;
tab style_innov if issue_daf==1;
tab style_disrupt if issue_daf==1;
tab neither if issue_daf==1;

tab style_trad if nso_daf==1;
tab style_innov if nso_daf==1;
tab style_disrupt if nso_daf==1;
tab neither if nso_daf==1;

** Focus of grantmaking;
tab focus_disc, m;
tab focus_disp, m;
tab focus_dei, m;
tab focus_un_sdg, m;

tab focus_disc if comm_daf==1;
tab focus_disp if comm_daf==1;
tab focus_dei if comm_daf==1;
tab focus_un_sdg if comm_daf==1;

tab focus_disc if issue_daf==1;
tab focus_disp if issue_daf==1;
tab focus_dei if issue_daf==1;
tab focus_un_sdg if issue_daf==1;

tab focus_disc if nso_daf==1;
tab focus_disp if nso_daf==1;
tab focus_dei if nso_daf==1;
tab focus_un_sdg if nso_daf==1;

** Features offered to donors;
tab feature_relation, m;
tab feature_impact, m;
tab feature_tool, m;

tab feature_relation if comm_daf==1;
tab feature_impact if comm_daf==1;
tab feature_tool if comm_daf==1;

tab feature_relation if issue_daf==1;
tab feature_impact if issue_daf==1;
tab feature_tool if issue_daf==1;

tab feature_relation if nso_daf==1;
tab feature_impact if nso_daf==1;
tab feature_tool if nso_daf==1;

** Isolate non-grantmaking DAFs to understand what they are;
preserve;
gen count=1;
keep if neither==1;
collapse (sum) count, by(naics);
gsort -count;
list in 1/10;
restore;
preserve;
keep if num_tot_grants==.;
keep if naics==813211|naics==813219|naics==813410|naics==611310|naics==813110;
gsort -assets_total_eoy;
br orgname assets_total_eoy naics;
restore;

** This is the James Andreoni and Ray Madoff method of calcualting DAF payout;
sum total_grants assets_total_boy contrib_all income_investment income_bonds;
replace income_investment=0 if income_investment==.;
replace income_bonds=0 if income_bonds==.;
gen alt_distr_ratio_am= max_grants/(assets_total_boy + contrib_all + income_investment + income_bonds);
replace alt_distr_ratio_am=1 if alt_distr_ratio_am>1 & alt_distr_ratio_am!=.;
replace contrib_noncash=0 if contrib_noncash==.;
gen nc_ratio=contrib_noncash/contrib_all;
gen all=1;
sum contr_gr, d;
save "temp.dta", replace;

foreach x in all comm_daf issue_daf nso_daf is_national is_incl style_trad style_innov style_disrupt 
focus_disc focus_disp focus_dei focus_un_sdg feature_relation feature_impact feature_tool {;
use "temp.dta";
keep if `x'==1;
gen count=1;
bys ein: gen unique=(_n==1);
gen tot_tot_grants=num_tot_grants;
collapse (sum) count (mean) daf_age assets_total_eoy contrib_all nc_ratio alt_distr_ratio_am;
replace assets_total_eoy=assets_total_eoy/1000000;
replace contrib_all=contrib_all/1000000;
gen type="`x'";
format daf_age %12.0fc;
save "temp`x'.dta", replace;
};

use "tempall.dta", clear;
foreach x in comm_daf issue_daf nso_daf is_national is_incl style_trad style_innov style_disrupt 
focus_disc focus_disp focus_dei focus_un_sdg feature_relation feature_impact feature_tool {;
append using "temp`x'.dta";
erase "temp`x'.dta";
};
save "tempfirstround.dta", replace;

foreach x in all comm_daf issue_daf nso_daf is_national is_incl style_trad style_innov style_disrupt 
focus_disc focus_disp focus_dei focus_un_sdg feature_relation feature_impact feature_tool {;
use "temp.dta";
keep if `x'==1;
collapse (mean) contr_gr [iweight=contrib_all_prior];
gen type="`x'";
rename contr_gr vw_contr_gr;
save "temp`x'.dta", replace;
};
use "tempall.dta", clear;
foreach x in comm_daf issue_daf nso_daf is_national is_incl style_trad style_innov style_disrupt 
focus_disc focus_disp focus_dei focus_un_sdg feature_relation feature_impact feature_tool {;
append using "temp`x'.dta";
erase "temp`x'.dta";
};
save "tempsecondround.dta", replace;

merge 1:1 type using "tempfirstround.dta";

keep type count daf_age assets_total_eoy contrib_all vw_contr_gr nc_ratio alt_distr_ratio_am;
order type count daf_age assets_total_eoy contrib_all vw_contr_gr nc_ratio alt_distr_ratio_am;

** Save as .tex;
texsave type count daf_age assets_total_eoy contrib_all vw_contr_gr nc_ratio alt_distr_ratio_am using "tex/Table2.tex", replace;
save "summary stats by daf type.dta", replace;



log close;
